USE [A3Fashion]

BEGIN TRANSACTION
/* Add Type*/
GO
ALTER TABLE dbo.Category ADD
	Type int NULL
GO

COMMIT

/* Add column "Name" to Color*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.Color_translation
	DROP CONSTRAINT fk_languageColor_pid
GO
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_Color_translation
	(
	ColorID int NOT NULL IDENTITY (1, 1),
	LanguageID varchar(10) NOT NULL,
	TranslationText varchar(50) NOT NULL,
	Name varchar(50) NOT NULL
	)  ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_Color_translation ON
GO
IF EXISTS(SELECT * FROM dbo.Color_translation)
	 EXEC('INSERT INTO dbo.Tmp_Color_translation (ColorID, LanguageID, TranslationText)
		SELECT ColorID, LanguageID, TranslationText FROM dbo.Color_translation WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_Color_translation OFF
GO
DROP TABLE dbo.Color_translation
GO
EXECUTE sp_rename N'dbo.Tmp_Color_translation', N'Color_translation', 'OBJECT' 
GO
ALTER TABLE dbo.Color_translation ADD CONSTRAINT
	PK__Color_translatio__0BC6C43E PRIMARY KEY CLUSTERED 
	(
	ColorID
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
ALTER TABLE dbo.Color_translation ADD CONSTRAINT
	fk_languageColor_pid FOREIGN KEY
	(
	LanguageID
	) REFERENCES dbo.Languages
	(
	LanguageID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
COMMIT

/* Rename TranslationText to Name in Category table*/
BEGIN TRANSACTION
GO
EXECUTE sp_rename N'dbo.Category_translation.TranslationText', N'Tmp_Name', 'COLUMN' 
GO
EXECUTE sp_rename N'dbo.Category_translation.Tmp_Name', N'Name', 'COLUMN' 
GO
COMMIT

/* Add Description and Name to Product table*/
BEGIN TRANSACTION
GO
ALTER TABLE dbo.Product_translation
	DROP CONSTRAINT fk_languageProduct_pid
GO
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_Product_translation
	(
	ProductID int NOT NULL,
	LanguageID varchar(10) NOT NULL,
	TranslationText varchar(255) NOT NULL,
	Description varchar(1000) NOT NULL,
	Name varchar(100) NOT NULL
	)  ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.Product_translation)
	 EXEC('INSERT INTO dbo.Tmp_Product_translation (ProductID, LanguageID, TranslationText)
		SELECT ProductID, LanguageID, TranslationText FROM dbo.Product_translation WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.Product_translation
GO
EXECUTE sp_rename N'dbo.Tmp_Product_translation', N'Product_translation', 'OBJECT' 
GO
ALTER TABLE dbo.Product_translation ADD CONSTRAINT
	PK__Product_translat__023D5A04 PRIMARY KEY CLUSTERED 
	(
	ProductID,
	LanguageID
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
ALTER TABLE dbo.Product_translation ADD CONSTRAINT
	fk_languageProduct_pid FOREIGN KEY
	(
	LanguageID
	) REFERENCES dbo.Languages
	(
	LanguageID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
COMMIT


/* Drop column TranslationText in Product & Color table*/
GO
ALTER TABLE [dbo].[Color_translation] DROP COLUMN [TranslationText]
GO

GO
ALTER TABLE [dbo].[Product_translation] DROP COLUMN [TranslationText]
GO
